<?php


namespace App\Console\Commands;

use App\Models\Warranty;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;
use Illuminate\Support\Str;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ReadNewExcel extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'command:readNewExcel';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {

        $con = storage_path() . '/app/upload/202108/';
        $excelName = Redis::get('excel_name');
        $id = 47;
        if (!empty($excelName)) {
            $excel =  DB::table('parse_excel')->where('type', 0)->where('excel_name', $excelName)->first();
            if (!empty($excel)) {
                $id = $excel->id;
            }
        }
        //$filename = scandir($con);
        $result = DB::table('parse_excel')->where('type', 0)->where('id', '>=', $id)->get();
        foreach ($result as $item) {
            echo $item->excel_name; echo PHP_EOL;
            Redis::set('excel_name', $item->excel_name);
            $this->parseExcel($con . $item->excel_name);
        }
        /*foreach($filename as $k=>$v){
            // 跳过两个特殊目录   continue跳出循环
            if($v=="." || $v=="..") {
                continue;
            }

            if (strpos($v, 'zip') !== false || strpos($v, 'DS_Store') !== false) {
                continue;
            }
            DB::table('parse_excel')->insert(['excel_name' => $v]);

            //$this->parseExcel($con . $v);
        }*/

        return 0;
    }

    public function parseExcel(string $file)
    {
        try {
            $spreadsheet = IOFactory::load($file);
        } catch (\Exception $exception) {
            return false;
        }
        $sheet = $spreadsheet->getActiveSheet();
        $vin_number = $sheet->getCell('F8')->getValue(); // 车架号
        $vin_number = strtoupper($vin_number);

        $warranty = DB::table('warranty')->where("vin_number", $vin_number)->where('created_at', '>=', '2021-07')->whereNull('deleted_at')->first();
        if (!empty($warranty)) {
            $image = DB::table('warranty_image')->where('warranty_id', $warranty->id)->whereNull('deleted_at')->get();
            $installation_images_zip_url = $spreadsheet->getActiveSheet()->getCell('G11')->getHyperlink()->getUrl(); // 安装图片地址
            $this->getInstalationImagesByZipUrl($installation_images_zip_url, $image);
            unset($spreadsheet);
            unset($sheet);
        }
    }

    protected function getInstalationImagesByZipUrl($url, $images)
    {
        if (empty($url)) {
            return false;
        }
        $filename = Str::random(16) . '.zip';
        $full_file_name =  storage_path() . '/app/upload/202108/' . $filename;
        $bool = file_put_contents($full_file_name, file_get_contents($url));
        $info = mime_content_type($full_file_name);
        if ($info == 'application/zip') {
            $zip = zip_open($full_file_name);
            $key = 0;
            while ($zip_entry = zip_read($zip)) {
                if (zip_entry_open($zip, $zip_entry, "r")) {
                    $file_name = zip_entry_name($zip_entry);
                    $pfn = pathinfo($file_name);
                    if (!empty($pfn['extension']) && in_array($pfn['extension'], ['jpg', 'jpeg', 'png', 'gif'])) {
                        $content = zip_entry_read($zip_entry,zip_entry_filesize($zip_entry));
                        if (empty($content)) {
                            continue;
                        }
                        if (empty($images[$key])) {
                            continue;
                        }
                        file_put_contents(public_path() . $images[$key]->image_src, $content);
                        unset($content);
                        $key++;
                    }
                }
            }
        }
    }

}